package com.server.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.server.database.SQLCreator;
import com.server.date.DateType;
import com.server.date.DateUtil;
import com.server.service.ISurveyPaperService;
import com.server.service.IUserAssignmentService;
import com.server.web.DataVector;
import com.server.web.RequestInput;

/**
 * 试卷
 * @author wufeng
 * @date 创建时间：2016年7月15日 上午10:36:38 
 */
@Service("SurveyPaperService")
public class SurveyPaperService implements ISurveyPaperService{
	private static String tableName = "education.survey";
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private CommonDataService commonDataService;
	@Autowired
	private IUserAssignmentService userAssignmentService;
	
	@Override
	public DataVector<Map<String, Object>> getList(String type,int state,String user,RequestInput requestInput){
		StringBuffer whereStr = new StringBuffer();
		if(type.equals("2")){
			 whereStr.append("creater").append("=").append("'").append(user).append("'");
		}
		if(type.equals("")){
			
		}
		if(whereStr.length() > 0){
			whereStr.append("and");
		}
		if(state != -1){
			whereStr.append(" state=").append("'").append(state).append("'");
		}
		String sortStr = " createTime desc";
		DataVector<Map<String, Object>> listData = commonDataService.getListData(tableName, whereStr.toString(), sortStr, requestInput);
		return listData;
	}
	
	@Override
	public boolean update(String id, Map<String, Object> inputMap, String operater) {
		boolean isSucess = commonDataService.update(operater, tableName, "id", id, inputMap);
		return isSucess;
	}
	
	@Override
	public Map<String, Object> getMap(String id) {
		Map<String, Object> map = commonDataService.getFormData("education.survey", "id", id, null, null);
		return map;
	}
	
	@Override
	public boolean delete(String id) {
		boolean isSuccess = true;
		String sql ="DELETE education.survey,education.survey_rela_survey_res FROM education.survey"
		+ " LEFT JOIN education.survey_rela_survey_res ON education.survey.id=education.survey_rela_survey_res.survey_id WHERE education.survey.id='"+id+"'";
		String sql2 = "select survey_res_id from education.survey_rela_survey_res where survey_id = '"+id+"' and valid=1";
		List<Map<String, Object>>  list = jdbcTemplate.queryForList(sql2);
		for (Map<String, Object> map : list) {
			Object obj = map.get("survey_res_id");
			if(!StringUtils.isEmpty(obj)){
				String survey_res_id = obj.toString();
				String sql3 = "update education.survey_res SET useCount = useCount-1 WHERE id = '"+survey_res_id+"'";
				try {
					int update = jdbcTemplate.update(sql3);
					if(update <= 0){
						isSuccess = false;
					}
				} catch (Exception e) {
					isSuccess =false;
				}
			}
		}
		try {
			int update = jdbcTemplate.update(sql);
			if(update <= 0){
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}
	

	@Override
	public Map<String, Object> add(Map<String, Object> inputMap, String operator) {
		Map<String, Object> inputParamMap = new HashMap<String, Object>();
		inputParamMap.put("name", inputMap.get("name"));//VARCHAR
		inputParamMap.put("direction", inputMap.get("direction"));//VARCHAR
		inputParamMap.put("scale", inputMap.get("scale"));//VARCHAR
		inputParamMap.put("creater", operator);
		Map<String, Object> execute = new SimpleJdbcCall(jdbcTemplate).withCatalogName("education")
			.withProcedureName("p_survey_add")
			.declareParameters(
						new SqlParameter("name",java.sql.Types.VARCHAR),
						new SqlParameter("direction",java.sql.Types.VARCHAR),
						new SqlParameter("scale",java.sql.Types.VARCHAR),
						new SqlParameter("creater",java.sql.Types.VARCHAR),
						new SqlOutParameter("id",java.sql.Types.INTEGER)
			).execute(inputParamMap);
		return execute;	
	}
	@Override
	public boolean addTestQuestions(String paperId, String testId, String type,String creater){
		String sql = "Insert into education.survey_rela_survey_res (survey_id,survey_res_id,type,creater) "
				+ "values ('"+paperId+"','"+testId+"','"+type+"','"+creater+"')";
		String sql2 = "update education.survey_res set useCount = useCount+1 where id = '"+testId+"' and valid = 1";
		boolean isSuccess = true;
		try {
			 int update = jdbcTemplate.update(sql);
			 if(update <= 0){
				 isSuccess =false;
			 }
		} catch (Exception e) {
			isSuccess =false;
		}
		
		if(isSuccess){
			try {
				int update = jdbcTemplate.update(sql2);
				if (update < 0) {
					isSuccess = false;
				}
			} catch (Exception e) {
				isSuccess = false;
			}
		}
		return isSuccess;
	}
	
	@Override
	public boolean isRight(String id, String operator) {
		String sql = "select creater from "+tableName+" where id = '"+id+"' and valid = 1";
		String authorship = jdbcTemplate.queryForObject(sql, String.class);
		if(operator.equals(authorship)){
			return true;
		}
		return false;
	}

	@Override
	public DataVector<Map<String, Object>> getUserList(RequestInput requestInput) {
		String _whereStr = "valid = 1";
		String sortStr = "createTime desc";
		StringBuffer whereStr = new StringBuffer();
		StringBuffer _sql = new StringBuffer();
		if (!StringUtils.isEmpty(_whereStr)) {
			whereStr.append(_whereStr);
		}
		Integer pageIndex = 0;
		Integer pageSize = Integer.MAX_VALUE;
		if (requestInput != null) {
			if (requestInput.getWhereStr() != null
					&& requestInput.getWhereStr().length() > 0) {
				if (!StringUtils.isEmpty(_whereStr)) {
					whereStr.append(" AND ");
				}
				whereStr.append("(").append(requestInput.getWhereStr())
						.append(")");
			}
			if (requestInput.getSortStr() != null
					&& requestInput.getSortStr().length() > 0) {
				sortStr = requestInput.getSortStr();
			}
			pageIndex = requestInput.getPageIndex();
			pageSize = requestInput.getPageSize();
		}
		Integer firstIndex = pageIndex*pageSize; 
		//Integer lastIndex = pageSize*(pageIndex+1);
		String sqlCount = SQLCreator.getSqlCount("education.user", whereStr.toString(),
				null, null);
		_sql.append("select id,name,nickname from education.user ").append("where ")
		.append(whereStr).append(" order by createTime").append(" LIMIT ").
		append(firstIndex).append(",").append(pageSize);
		//String sql_2 = "select name,nickname from education.user "
		/*String sql = SQLCreator.getSql_Mysql(pageIndex, pageSize, "education.user",
				whereStr.toString(), sortStr, null, null);*/
		List<Map<String, Object>> list = jdbcTemplate.queryForList(_sql.toString());
		Integer count = jdbcTemplate.queryForObject(sqlCount, Integer.class);
		return new DataVector<Map<String, Object>>(list, count);
	}

	@Override
	public Map<String, Object> getUserData(String id) {
		String sqlString = "select id,name,nickname,uuid from education.user where id = '"+id+"'";
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			map = jdbcTemplate.queryForMap(sqlString);
		} catch (Exception e) {
			return new HashMap<String, Object>();
		}
		return map;
	}

	@Override
	public Map<String, Object> getMapData(String paperId) {
		String sqlString = "select object,object_uuid from education.survey where id = '"+paperId+"'";
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			map = jdbcTemplate.queryForMap(sqlString);
		} catch (Exception e) {
			return new HashMap<String, Object>();
		}
		return map;
	}

	@Override
	public boolean addObject(String paperId, String object, String uuid) {
		boolean isSuccess = true;
		String sql = "update education.survey set object ='"+object+"',object_uuid = '"+uuid+"' where id = '"+paperId+"' and valid=1";
		try {
			int update = jdbcTemplate.update(sql);
			if(update <= 0){
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess =false;
		}
		return isSuccess;
	}

	@Override
	public DataVector<Map<String, Object>> getQuetionsList(String type,
			RequestInput requestInput) {
		String whereStr = "class_id = '"+type+"'";
		DataVector<Map<String, Object>> reDataVector = commonDataService.getListData("education.survey_res", whereStr, null, requestInput);
		return reDataVector;
	}

	@Override
	public List<Map<String, Object>> getQuestionIdList(String paperId){
		String sql = "select survey_res_id from education.survey_rela_survey_res where survey_id = '"+paperId+"'";
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		try {
			list = jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			return new ArrayList<Map<String,Object>>();
		}
		return list;
		
	}
	@Override
	public boolean updateState(String id, int state, String operator, Boolean isAudit) {
		boolean isSuccess = true;
		String sql = null;
		if (isAudit) {
			sql = "update %s set state=%s,auditor='%s',auditTime='%s' where id=%s";
		}else {
			sql = "update %s set state=%s,evaluator='%s',evalueTime='%s' where id=%s";
		}
		String dateStr = DateUtil.getDateStr(new Date(),DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		try {
			int update = jdbcTemplate.update(String.format(sql, tableName,state,operator,dateStr,id));
			if(update <= 0){
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}
	@Override
	public boolean deleteTest(String id,String testId){
		boolean isSuccess = true;
		String sql3 = "delete from education.survey_rela_survey_res where "
				+ "survey_id = '"+id+"' and survey_res_id = '"+testId+"'";
		//String sql = "update education.exam set sumScore = 0 where id ='"+id+"'";
		try {
			 int update = jdbcTemplate.update(sql3);
			// int update1 = jdbcTemplate.update(sql);
			 if(update < 0){
				 isSuccess =false;
			 }
		} catch (Exception e) {
			isSuccess =false;
		}
		return isSuccess;
	}
	
	@Override
	public Integer toEstimate(String ids,String operator){
		StringBuffer idsBuffer = new StringBuffer();
		String[] split = ids.split(",");
		for(String id : split){
			Boolean estimateAssi = userAssignmentService.estimateAssi(Integer.parseInt(id), 4, 1);
			if (estimateAssi) {
				if (idsBuffer.length() > 0) {
					idsBuffer.append(",");
				}
				idsBuffer.append(id);
			}
		}
		if (idsBuffer.length() <= 0) {
			return 0;
		}
		String dateStr = DateUtil.getDateStr(new Date(),DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		String sql = "update %s set state=1,mender='%s',updateTime='%s' where id in('%s')";
		return jdbcTemplate.update(String.format(sql, tableName,operator,dateStr,idsBuffer.toString().replaceAll(",", "','")));
	}
	
	@Override
	public List<Map<String, Object>> list(String paperId) {
		String sql ="select survey_res_id from education.survey_rela_survey_res where survey_id ='"+paperId+"'";
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		try {
			list = jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			return new ArrayList<Map<String,Object>>();
		}
		return list;
	}

	@Override
	public Integer distribute(String operator,
			List<String> users,Map<String, Object> inputMap){
		StringBuffer fieldBuffer = new StringBuffer("creater,createTime");
		StringBuffer valueBuffer = new StringBuffer();
		String dateStr = DateUtil.getDateStr(new Date(),
				DateType.YYYY_MM_DD_HH_mm_ss_SSS);
		valueBuffer.append("'").append(operator).append("','").append(dateStr)
				.append("'");
		Set<String> keySet = inputMap.keySet();
		for (String key : keySet) {
			if ("creater".equals(key) || "createTime".equals(key)) {
				continue;
			}
			Object valueObj = inputMap.get(key);
			if (valueObj == null || StringUtils.isEmpty(valueObj.toString())) {
				continue;
			}
			fieldBuffer.append(",");
			fieldBuffer.append(key);
			valueBuffer.append(",");
			valueBuffer.append("'").append(valueObj).append("'");
		}
		Iterator<String> iterator = users.iterator();
		String[] sqls = new String[users.size()];
		int i = 0;
		while(iterator.hasNext()){
			StringBuffer sqlBuffer = new StringBuffer();
			String user = iterator.next();
			sqlBuffer.append("insert into ").append("education.survey_distribute").append("(")
			.append(fieldBuffer).append(",user_uuid").append(")").append(" values(")
			.append(valueBuffer).append(",'").append(user).append("'); ");
			sqls[i++] = sqlBuffer.toString();
		}
		int[] batchUpdate = jdbcTemplate.batchUpdate(sqls);
		return batchUpdate.length;
	}
	
	@Override
	public boolean distribute(String paperId, String operator, String object) {
		boolean isSuccess = true;
		String sql = "Insert into education.survey_distribute (surveyId,nickname,creater)"
				+ " values ('"+paperId+"','"+object+"','"+operator+"')";
		try {
			int update = jdbcTemplate.update(sql);
			if(update<1){
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		return isSuccess;
	}

	@Override
	public boolean change(String paperId, String field, Integer value) {
		boolean isSuccess = true;
		String sql = "update education.survey set "+field+" = '"+value+"' where id = '"+paperId+"'";
		try {
			int update = jdbcTemplate.update(sql);
			if(update<1){
				isSuccess = false;
			}
		} catch (Exception e) {
			isSuccess = false;
		}
		
		return isSuccess;
	}
	
	
}
